---
id: inspect
slug: /declarative/inspect
title: Inspecting existing schemas with Atlas
---

import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';

### Automatic Schema Inspection
Many projects begin with an existing database that users wish to start managing
with Atlas. In this case, instead of having developers learn the [Atlas Language](/atlas-schema/hcl)
and reverse engineer a schema definition file that precisely describes the existing database,
Atlas supports _automatic schema inspection_.

With automatic schema inspection, users simply provide Atlas with a connection string
to their target database and Atlas prints out a schema definition file in the Atlas
language that they can use as the starting point for working with this database.

:::info Login to Atlas
By default, running `atlas schema inspect` inspects only **schemas**, **tables**, and their associated **indexes** and
constraints such as **foreign keys** and **checks**.

**Views**, **materialized views**, **functions**, **procedures**, **triggers**, **sequences**, **domains** and additional
beta features are available to logged-in users only. To include these resources in the inspection, use the following command:

```shell
atlas login
```
:::

## Flags

When using `schema inspect` to inspect an existing database, users may supply multiple
parameters:
* `--url` (`-u` accepted as well) - the [URL](/concepts/url) of database to be inspected.
* `--schema` (optional, may be supplied multiple times) - schemas to inspect within
 the target database.
* `--exclude` (optional) - filter out resources matching the given glob pattern.
* `--format` (optional) - [Go template](https://pkg.go.dev/text/template) to use to format the output.
* `--web` (`-w` accepted as well) - visualize the schema as an ERD on [Atlas Cloud](https://auth.atlasgo.cloud/signup). See an
  example [here](https://gh.atlasgo.cloud/explore/40d83919).

## Examples

### Inspect a database

The following commands demonstrate how to inspect the entire database, including all its schemas:

<Tabs defaultValue="mysql">
<TabItem label="MySQL" value="mysql">

```shell
atlas schema inspect -u "mysql://localhost"

atlas schema inspect -u "mysql://user:pass@localhost:3306"
```

</TabItem>
<TabItem label="MariaDB" value="maria">

```shell
atlas schema inspect -u "maria://localhost"

atlas schema inspect -u "maria://user:pass@localhost:3306"
```

</TabItem>
<TabItem label="PostgreSQL" value="postgres">

```shell
atlas schema inspect -u "postgres://localhost:5432/database"

atlas schema inspect -u "postgres://postgres:pass@0.0.0.0:5432/database?sslmode=disable"
```

</TabItem>
<TabItem label="SQLite" value="sqlite">

```shell
atlas schema inspect -u "sqlite://file.db"

atlas schema inspect -u "sqlite://file?cache=shared&mode=memory"
```

</TabItem>
<TabItem label="SQL Server" value="sqlserver">

```shell
atlas schema inspect -u "sqlserver://user:pass@localhost:1433?database=master&mode=database"

atlas schema inspect -u "sqlserver://user:pass@0.0.0.0:1433?database=master&mode=database"
```

</TabItem>
<TabItem label="ClickHouse" value="clickhouse">

```shell
atlas schema inspect -u "clickhouse://localhost:9000"

atlas schema inspect -u "clickhouse://user:pass@0.0.0.0:9000?secure=true"
```

</TabItem>
</Tabs>

### Inspect a schema

The following commands show how to inspect a single schema:

<Tabs defaultValue="mysql">
<TabItem label="MySQL" value="mysql">

```shell
atlas schema inspect -u "mysql://localhost/schema"

atlas schema inspect -u "mysql://user:pass@localhost:3306/schema"
```

</TabItem>
<TabItem label="MariaDB" value="maria">

```shell
atlas schema inspect -u "maria://localhost/schema"

atlas schema inspect -u "maria://user:pass@localhost:3306/schema"
```

</TabItem>
<TabItem label="PostgreSQL" value="postgres">

```shell
atlas schema inspect -u "postgres://localhost:5432/database?search_path=public"

atlas schema inspect -u "postgres://postgres:pass@0.0.0.0:5432/database?search_path=public&sslmode=disable"
```

</TabItem>
<TabItem label="SQL Server" value="sqlserver">

```shell
atlas schema inspect -u "sqlserver://user:pass@localhost:1433?database=master"

atlas schema inspect -u "sqlserver://user:pass@0.0.0.0:1433?database=master"
```

</TabItem>
<TabItem label="ClickHouse" value="clickhouse">

```shell
atlas schema inspect -u "clickhouse://localhost:9000/database"

atlas schema inspect -u "clickhouse://user:pass@0.0.0.0:9000/database?secure=true"
```

</TabItem>
</Tabs>

### Inspect multiple schemas

The following commands show how to inspect multiple schemas:

<Tabs defaultValue="mysql">
<TabItem label="MySQL" value="mysql">

```shell
atlas schema inspect -u "mysql://localhost" --schema schema1 --schema schema2

atlas schema inspect -u "mysql://user:pass@localhost:3306" -s schema1,schema2
```

</TabItem>
<TabItem label="MariaDB" value="maria">

```shell
atlas schema inspect -u "maria://localhost" --schema schema1 --schema schema2

atlas schema inspect -u "maria://user:pass@localhost:3306" -s schema1,schema2
```

</TabItem>
<TabItem label="PostgreSQL" value="postgres">

```shell
atlas schema inspect -u "postgres://localhost:5432/database" --schema schema1 --schema schema2

atlas schema inspect -u "postgres://postgres:pass@0.0.0.0:5432/database?sslmode=disable" -s schema1,schema2
```

</TabItem>
<TabItem label="SQL Server" value="sqlserver">

```shell
atlas schema inspect -u "sqlserver://user:pass@localhost:1433?database=master&mode=database" --schema schema1 --schema schema2

atlas schema inspect -u "sqlserver://user:pass@0.0.0.0:1433?database=master&mode=database" -s schema1,schema2
```

</TabItem>
<TabItem label="ClickHouse" value="clickhouse">

```shell
atlas schema inspect -u "clickhouse://localhost:9000/database" --schema schema1 --schema schema2

atlas schema inspect -u "clickhouse://user:pass@0.0.0.0:9000/database?secure=true" -s schema1,schema2
```

</TabItem>
</Tabs>

### Exclude schemas

The following commands show how to exclude schemas that match a glob pattern from the inspection:

```shell
atlas schema inspect -u "mysql://localhost" --exclude "internal"

atlas schema inspect -u "mysql://localhost" --exclude "schema_*"
```

### Exclude Tables

The following commands show how to exclude tables that match a glob pattern from the inspection:

<Tabs>
<TabItem value="database-scope" label="Database Scope">

When inspecting a database (multiple schemas), the first glob pattern matches the schema name, and the second matches
the table name:

```shell
atlas schema inspect -u "mysql://localhost" --exclude "*.prefix_*"

atlas schema inspect -u "mysql://localhost" --exclude "schema.table"

atlas schema inspect -u "mysql://localhost" --exclude "schema.t*[type=table]" --exclude "schema.e*[type=enum]"
```

</TabItem>
<TabItem value="schema-scope" label="Schema Scope">

When inspecting a specific schema, the first glob pattern matches the table name:

```shell
atlas schema inspect -u "mysql://localhost" --exclude "prefix_*"

atlas schema inspect -u "mysql://localhost" --exclude "table"

atlas schema inspect -u "mysql://localhost" --exclude "t*[type=table]" --exclude "e*[type=enum]"
```

</TabItem>
</Tabs>

### Exclude Table Resources

The following commands show how to exclude columns, indexes or foreign-keys that match a glob pattern from the inspection:


<Tabs>
<TabItem value="database-scope" label="Database Scope">

When inspecting a database (multiple schemas), the first glob pattern matches the schema name, and the second matches
the table name:

```shell
atlas schema inspect -u "mysql://localhost" --exclude "*.*.prefix_*"

atlas schema inspect -u "mysql://localhost" --exclude "public.*.c1"

atlas schema inspect -u "mysql://localhost" --exclude "public.*.c*[type=column|index]"
```

</TabItem>
<TabItem value="schema-scope" label="Schema Scope">

When inspecting a specific schema, the first glob pattern matches the table name:

```shell
atlas schema inspect -u "mysql://localhost" --exclude "*.prefix_*"

atlas schema inspect -u "mysql://localhost" --exclude "*.c1"

atlas schema inspect -u "mysql://localhost" --exclude "*.c*[type=column|index]"
```

</TabItem>
</Tabs>

### SQL Format

By default, the output of `schema inspect` is in the Atlas DDL. However, you can use SQL to describe the desired schema
in all commands that are supported by Atlas DDL. To output the schema in SQL format, use the `--format` flag as follows:

```shell
atlas schema inspect -u "mysql://localhost" --format "{{ sql . }}"
```

### JSON Format

Atlas can output a JSON document that represents the database schema. This representation allows users to use tools
like `jq` to analyze the schema programmatically.

```shell
atlas schema inspect -u '<url>' --format '{{ json . }}'
```

### Visualize schemas

Atlas can generate an Entity Relationship Diagram (ERD) for the inspected schemas. The following command shows how to
generate an ERD for inspected schemas:

```shell
atlas schema inspect -u '<url>' -w
```

<p style={{textAlign: "center"}}>
    <a href="https://gh.atlasgo.cloud/explore/40d83919">
        <img src="https://atlasgo.io/uploads/erd-example.png" alt="Schema ERD"/>
    </a>
</p>

## Reference

[CLI Command Reference](/cli-reference#atlas-schema-inspect)
